/**
 * SQLite3数据库初始化模块
 * model\sqlite\init.js
 * 1. npm install sqlite3
 * 2. API文档:https://github.com/TryGhost/node-sqlite3/wiki/API
 */
 
//导入Node.js文件模块
const fs = require("fs");
//导入SQLite3模块
const sqlite3 = require("sqlite3").verbose();
//导入工具模块
const util = require("../../common/util");
//导入配置模块
const config = require("../../common/config");


//sql语句，创建实验室设备表
const sql_create_lab_device = `
CREATE TABLE IF NOT EXISTS lab_device(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    imageurl TEXT,
    brand TEXT,
    spcialtype TEXT,
    status TEXT,  -- 启用时间
    device_status TEXT DEFAULT 'available',  -- 设备状态：available(可用), borrowed(已借出), maintenance(维护中)
    category TEXT,
    identify TEXT,
    price TEXT,
    description TEXT,
    danwei TEXT,
    contact TEXT,
    phone TEXT,
    email TEXT,
    location TEXT,
    xiamenId TEXT,
    technicalSpecs TEXT,
    mainFunctions TEXT,
    serviceContent TEXT,
    openSharingRules TEXT,
    created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_time DATETIME DEFAULT CURRENT_TIMESTAMP
);`;



//sql语句，创建user数据表
const sqlcreateUser = `
CREATE TABLE IF NOT EXISTS users(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    nickname VARCHAR(255),
    truename VARCHAR(255),
    avatar VARCHAR(255),
    role INTERGER NOT NULL,
    last_login_time DATETIME NOT NULL,
    last_login_ip VARCHAR(255) NOT NULL, 
    login_count INTERGER DEFAULT 0,
    created_time DATETIME NOT NULL,
    created_ip VARCHAR(255) NOT NULL,
    updated_time DATETIME NOT NULL
);
`;

//sql语句，创建设备分类表
const sql_device_category = `
CREATE TABLE device_categories (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  description TEXT,
  created_at DATETIME DEFAULT (datetime('now','localtime')),
  updated_at DATETIME DEFAULT (datetime('now','localtime'))
);`;

//sql语句，创建borrow_records借用记录表
const sql_create_borrow_records = `
CREATE TABLE IF NOT EXISTS borrow_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    device_id INTEGER NOT NULL,
    borrow_time DATETIME NOT NULL,
    return_time DATETIME,
    start_time DATETIME,
    end_time DATETIME,
    purpose TEXT,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    remarks TEXT,
    created_time DATETIME NOT NULL,
    updated_time DATETIME NOT NULL
);`;

//sql语句，创建设备使用记录表
const sql_create_device_usage_records = `
CREATE TABLE IF NOT EXISTS device_usage_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    device_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    usage_note TEXT NOT NULL,
    created_time DATETIME NOT NULL,
    FOREIGN KEY (device_id) REFERENCES lab_device(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);`;

//sql语句，创建设备评价表
const sql_create_device_feedback = `
CREATE TABLE IF NOT EXISTS device_feedback (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    device_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    feedback TEXT NOT NULL,
    created_time DATETIME NOT NULL,
    FOREIGN KEY (device_id) REFERENCES lab_device(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);`;

//sql语句，创建评价点赞表
const sql_create_feedback_likes = `
CREATE TABLE IF NOT EXISTS feedback_likes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    feedback_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    created_time DATETIME DEFAULT (datetime('now','localtime')),
    type TEXT NOT NULL DEFAULT 'like',
    UNIQUE(feedback_id, user_id)
);`;

//sql语句，创建user数据表中的第一个用户
const sql = `
  INSERT INTO users(
        username, password, nickname, truename, avatar,
        role, last_login_time, last_login_ip, login_count, created_time,
        created_ip, updated_time
    ) VALUES (
      'admin', '${util.md5pwd("123456")}', '系统管理员', '超级管理员', './images/default.png', 
      1, 0, 'never login', 0, ${Date.now()}, 'system init', ${Date.now()}
    );`;



/**
 * SqliteDB构造函数
 * @constructor
 */
    function SqliteDB() {
        this.dbFile = config.dbfile;
        //如果数据库nylibrary.db文件不存在则创建数据库文件
        if (!fs.existsSync(this.dbFile)) {
          util.log("创建SQLite数据库文件【mylabs.db】");
          this.initDB();
        }
      }
       
      /**
       * 执行初始化数据库的SQL语句
       */
      SqliteDB.prototype.initDB = function () {
        let db = new sqlite3.Database(this.dbFile, (err) => {
          if (err) {
            util.err(err);
          }
        });
        db.serialize(() => {
          db.run(sql_create_lab_device);
          util.log("数据表lab_device创建成功");
          db.run(sqlcreateUser);
          util.log("数据表users创建成功");
          db.run(sql_device_category);
          util.log("数据表device_categories创建成功");
          db.run(sql_create_borrow_records);
          util.log("数据表borrow_records创建成功");
          db.run(sql_create_device_usage_records);
          util.log("数据表device_usage_records创建成功");
          db.run(sql_create_device_feedback);
          util.log("数据表device_feedback创建成功");
          db.run(sql_create_feedback_likes);
          util.log("数据表feedback_likes创建成功");
          db.run(sql);
          util.log("数据表users数据创建成功");
        });
        //SQLite数据库是基于文件，大量的资源消耗在文件的打开与关闭上会降低效率
      //   db.close((err) => {
      //     if (err) {
      //       util.err(err);
      //     }
      //   });
      };
       
module.exports = SqliteDB